{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Self join\n",
    "\n",
    "## Edinburgh Buses\n",
    "[Details of the database](https://sqlzoo.net/wiki/Edinburgh_Buses.) Looking at the data\n",
    "\n",
    "```\n",
    "stops(id, name)\n",
    "route(num, company, pos, stop)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "How many **stops** are in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops <- dbReadTable(con, 'stops')\n",
    "route <- dbReadTable(con, 'route')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>246</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " n\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 246\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| n &lt;int&gt; |\n",
       "|---|\n",
       "| 246 |\n",
       "\n"
      ],
      "text/plain": [
       "  n  \n",
       "1 246"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stops %>% tally"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Find the **id** value for the stop 'Craiglockhart'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>53</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " id\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 53\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| id &lt;int&gt; |\n",
       "|---|\n",
       "| 53 |\n",
       "\n"
      ],
      "text/plain": [
       "  id\n",
       "1 53"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stops %>% \n",
    "    filter(name=='Craiglockhart') %>% \n",
    "    select(id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Give the **id** and the **name** for the **stops** on the '4' 'LRT' service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 9 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td> 19</td><td>Bingham       </td></tr>\n",
       "\t<tr><td> 53</td><td>Craiglockhart </td></tr>\n",
       "\t<tr><td> 85</td><td>Fairmilehead  </td></tr>\n",
       "\t<tr><td>115</td><td>Haymarket     </td></tr>\n",
       "\t<tr><td>117</td><td>Hillend       </td></tr>\n",
       "\t<tr><td>149</td><td>London Road   </td></tr>\n",
       "\t<tr><td>177</td><td>Northfield    </td></tr>\n",
       "\t<tr><td>179</td><td>Oxgangs       </td></tr>\n",
       "\t<tr><td>194</td><td>Princes Street</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 9 × 2\n",
       "\\begin{tabular}{ll}\n",
       " id & name\\\\\n",
       " <int> & <chr>\\\\\n",
       "\\hline\n",
       "\t  19 & Bingham       \\\\\n",
       "\t  53 & Craiglockhart \\\\\n",
       "\t  85 & Fairmilehead  \\\\\n",
       "\t 115 & Haymarket     \\\\\n",
       "\t 117 & Hillend       \\\\\n",
       "\t 149 & London Road   \\\\\n",
       "\t 177 & Northfield    \\\\\n",
       "\t 179 & Oxgangs       \\\\\n",
       "\t 194 & Princes Street\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 9 × 2\n",
       "\n",
       "| id &lt;int&gt; | name &lt;chr&gt; |\n",
       "|---|---|\n",
       "|  19 | Bingham        |\n",
       "|  53 | Craiglockhart  |\n",
       "|  85 | Fairmilehead   |\n",
       "| 115 | Haymarket      |\n",
       "| 117 | Hillend        |\n",
       "| 149 | London Road    |\n",
       "| 177 | Northfield     |\n",
       "| 179 | Oxgangs        |\n",
       "| 194 | Princes Street |\n",
       "\n"
      ],
      "text/plain": [
       "  id  name          \n",
       "1  19 Bingham       \n",
       "2  53 Craiglockhart \n",
       "3  85 Fairmilehead  \n",
       "4 115 Haymarket     \n",
       "5 117 Hillend       \n",
       "6 149 London Road   \n",
       "7 177 Northfield    \n",
       "8 179 Oxgangs       \n",
       "9 194 Princes Street"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stops %>% \n",
    "    inner_join(route, by=c(id=\"stop\")) %>%\n",
    "    filter(num=='4' & company=='LRT') %>%\n",
    "    select(id, name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Routes and stops\n",
    "\n",
    "The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 2 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>company</th><th scope=col>num</th><th scope=col>n_route</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>LRT</td><td>4 </td><td>2</td></tr>\n",
       "\t<tr><td>LRT</td><td>45</td><td>2</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 2 × 3\n",
       "\\begin{tabular}{lll}\n",
       " company & num & n\\_route\\\\\n",
       " <chr> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t LRT & 4  & 2\\\\\n",
       "\t LRT & 45 & 2\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 2 × 3\n",
       "\n",
       "| company &lt;chr&gt; | num &lt;chr&gt; | n_route &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| LRT | 4  | 2 |\n",
       "| LRT | 45 | 2 |\n",
       "\n"
      ],
      "text/plain": [
       "  company num n_route\n",
       "1 LRT     4   2      \n",
       "2 LRT     45  2      "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    filter(stop==149 | stop==53) %>%\n",
    "    group_by(company, num) %>% \n",
    "    summarise(n_route=n()) %>%\n",
    "    filter(n_route==2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 2 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>company</th><th scope=col>num</th><th scope=col>stop.x</th><th scope=col>stop.y</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>LRT</td><td>4 </td><td>53</td><td>149</td></tr>\n",
       "\t<tr><td>LRT</td><td>45</td><td>53</td><td>149</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 2 × 4\n",
       "\\begin{tabular}{llll}\n",
       " company & num & stop.x & stop.y\\\\\n",
       " <chr> & <chr> & <int> & <int>\\\\\n",
       "\\hline\n",
       "\t LRT & 4  & 53 & 149\\\\\n",
       "\t LRT & 45 & 53 & 149\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 2 × 4\n",
       "\n",
       "| company &lt;chr&gt; | num &lt;chr&gt; | stop.x &lt;int&gt; | stop.y &lt;int&gt; |\n",
       "|---|---|---|---|\n",
       "| LRT | 4  | 53 | 149 |\n",
       "| LRT | 45 | 53 | 149 |\n",
       "\n"
      ],
      "text/plain": [
       "  company num stop.x stop.y\n",
       "1 LRT     4   53     149   \n",
       "2 LRT     45  53     149   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    inner_join(route, by=c(company=\"company\", num=\"num\")) %>%\n",
    "    filter(stop.x==53 & stop.y==149) %>%\n",
    "    select(company, num, stop.x, stop.y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "The query shown is similar to the previous one, however by joining two copies of the **stops** table we can refer to **stops** by **name** rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 2 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>company</th><th scope=col>num</th><th scope=col>name.x</th><th scope=col>name.y</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>LRT</td><td>4 </td><td>Craiglockhart</td><td>London Road</td></tr>\n",
       "\t<tr><td>LRT</td><td>45</td><td>Craiglockhart</td><td>London Road</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 2 × 4\n",
       "\\begin{tabular}{llll}\n",
       " company & num & name.x & name.y\\\\\n",
       " <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t LRT & 4  & Craiglockhart & London Road\\\\\n",
       "\t LRT & 45 & Craiglockhart & London Road\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 2 × 4\n",
       "\n",
       "| company &lt;chr&gt; | num &lt;chr&gt; | name.x &lt;chr&gt; | name.y &lt;chr&gt; |\n",
       "|---|---|---|---|\n",
       "| LRT | 4  | Craiglockhart | London Road |\n",
       "| LRT | 45 | Craiglockhart | London Road |\n",
       "\n"
      ],
      "text/plain": [
       "  company num name.x        name.y     \n",
       "1 LRT     4   Craiglockhart London Road\n",
       "2 LRT     45  Craiglockhart London Road"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    inner_join(stops, by=c(stop=\"id\")) %>% \n",
    "    inner_join(route %>%\n",
    "               inner_join(stops, by=c(stop=\"id\")),\n",
    "               by=c(company=\"company\", num=\"num\")\n",
    "              ) %>%\n",
    "    filter(name.x=='Craiglockhart' &\n",
    "           name.y=='London Road') %>%\n",
    "    select(company, num, name.x, name.y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. [Using a self join](https://sqlzoo.net/wiki/Using_a_self_join)\n",
    "\n",
    "Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>company</th><th scope=col>num</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>LRT</td><td>12</td></tr>\n",
       "\t<tr><td>LRT</td><td>2 </td></tr>\n",
       "\t<tr><td>LRT</td><td>22</td></tr>\n",
       "\t<tr><td>LRT</td><td>25</td></tr>\n",
       "\t<tr><td>LRT</td><td>2A</td></tr>\n",
       "\t<tr><td>SMT</td><td>C5</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " company & num\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t LRT & 12\\\\\n",
       "\t LRT & 2 \\\\\n",
       "\t LRT & 22\\\\\n",
       "\t LRT & 25\\\\\n",
       "\t LRT & 2A\\\\\n",
       "\t SMT & C5\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| company &lt;chr&gt; | num &lt;chr&gt; |\n",
       "|---|---|\n",
       "| LRT | 12 |\n",
       "| LRT | 2  |\n",
       "| LRT | 22 |\n",
       "| LRT | 25 |\n",
       "| LRT | 2A |\n",
       "| SMT | C5 |\n",
       "\n"
      ],
      "text/plain": [
       "  company num\n",
       "1 LRT     12 \n",
       "2 LRT     2  \n",
       "3 LRT     22 \n",
       "4 LRT     25 \n",
       "5 LRT     2A \n",
       "6 SMT     C5 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    inner_join(route, by=c(company=\"company\", num=\"num\")) %>%\n",
    "    filter(stop.x==115 & stop.y==137) %>%\n",
    "    distinct(company, num)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>company</th><th scope=col>num</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>LRT</td><td>27</td></tr>\n",
       "\t<tr><td>LRT</td><td>45</td></tr>\n",
       "\t<tr><td>LRT</td><td>47</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 2\n",
       "\\begin{tabular}{ll}\n",
       " company & num\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t LRT & 10\\\\\n",
       "\t LRT & 27\\\\\n",
       "\t LRT & 45\\\\\n",
       "\t LRT & 47\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 2\n",
       "\n",
       "| company &lt;chr&gt; | num &lt;chr&gt; |\n",
       "|---|---|\n",
       "| LRT | 10 |\n",
       "| LRT | 27 |\n",
       "| LRT | 45 |\n",
       "| LRT | 47 |\n",
       "\n"
      ],
      "text/plain": [
       "  company num\n",
       "1 LRT     10 \n",
       "2 LRT     27 \n",
       "3 LRT     45 \n",
       "4 LRT     47 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    inner_join(stops, by=c(stop=\"id\")) %>% \n",
    "    inner_join(route %>%\n",
    "               inner_join(stops, by=c(stop=\"id\")),\n",
    "               by=c(company=\"company\", num=\"num\")\n",
    "              ) %>%\n",
    "    filter(name.x=='Craiglockhart' & \n",
    "           name.y=='Tollcross') %>%\n",
    "    distinct(company, num)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "Give a distinct list of the **stops** which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 46 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name.y</th><th scope=col>company</th><th scope=col>num</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Colinton      </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Craiglockhart </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Leith         </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Leith Walk    </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Muirhouse     </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Newhaven      </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Princes Street</td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Silverknowes  </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Tollcross     </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>Torphin       </td><td>LRT</td><td>10</td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>Tollcross        </td><td>LRT</td><td>45</td></tr>\n",
       "\t<tr><td>Balerno          </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Balerno Church   </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Canonmills       </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Cockburn Crescent</td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Colinton         </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Craiglockhart    </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Currie           </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Hanover Street   </td><td>LRT</td><td>47</td></tr>\n",
       "\t<tr><td>Tollcross        </td><td>LRT</td><td>47</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 46 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name.y & company & num\\\\\n",
       " <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Colinton       & LRT & 10\\\\\n",
       "\t Craiglockhart  & LRT & 10\\\\\n",
       "\t Leith          & LRT & 10\\\\\n",
       "\t Leith Walk     & LRT & 10\\\\\n",
       "\t Muirhouse      & LRT & 10\\\\\n",
       "\t Newhaven       & LRT & 10\\\\\n",
       "\t Princes Street & LRT & 10\\\\\n",
       "\t Silverknowes   & LRT & 10\\\\\n",
       "\t Tollcross      & LRT & 10\\\\\n",
       "\t Torphin        & LRT & 10\\\\\n",
       "\t ... & ... & ...\\\\\n",
       "\t Tollcross         & LRT & 45\\\\\n",
       "\t Balerno           & LRT & 47\\\\\n",
       "\t Balerno Church    & LRT & 47\\\\\n",
       "\t Canonmills        & LRT & 47\\\\\n",
       "\t Cockburn Crescent & LRT & 47\\\\\n",
       "\t Colinton          & LRT & 47\\\\\n",
       "\t Craiglockhart     & LRT & 47\\\\\n",
       "\t Currie            & LRT & 47\\\\\n",
       "\t Hanover Street    & LRT & 47\\\\\n",
       "\t Tollcross         & LRT & 47\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 46 × 3\n",
       "\n",
       "| name.y &lt;chr&gt; | company &lt;chr&gt; | num &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| Colinton       | LRT | 10 |\n",
       "| Craiglockhart  | LRT | 10 |\n",
       "| Leith          | LRT | 10 |\n",
       "| Leith Walk     | LRT | 10 |\n",
       "| Muirhouse      | LRT | 10 |\n",
       "| Newhaven       | LRT | 10 |\n",
       "| Princes Street | LRT | 10 |\n",
       "| Silverknowes   | LRT | 10 |\n",
       "| Tollcross      | LRT | 10 |\n",
       "| Torphin        | LRT | 10 |\n",
       "| ... | ... | ... |\n",
       "| Tollcross         | LRT | 45 |\n",
       "| Balerno           | LRT | 47 |\n",
       "| Balerno Church    | LRT | 47 |\n",
       "| Canonmills        | LRT | 47 |\n",
       "| Cockburn Crescent | LRT | 47 |\n",
       "| Colinton          | LRT | 47 |\n",
       "| Craiglockhart     | LRT | 47 |\n",
       "| Currie            | LRT | 47 |\n",
       "| Hanover Street    | LRT | 47 |\n",
       "| Tollcross         | LRT | 47 |\n",
       "\n"
      ],
      "text/plain": [
       "    name.y            company num\n",
       "1   Colinton          LRT     10 \n",
       "2   Craiglockhart     LRT     10 \n",
       "3   Leith             LRT     10 \n",
       "4   Leith Walk        LRT     10 \n",
       "5   Muirhouse         LRT     10 \n",
       "6   Newhaven          LRT     10 \n",
       "7   Princes Street    LRT     10 \n",
       "8   Silverknowes      LRT     10 \n",
       "9   Tollcross         LRT     10 \n",
       "10  Torphin           LRT     10 \n",
       "... ...               ...     ...\n",
       "37  Tollcross         LRT     45 \n",
       "38  Balerno           LRT     47 \n",
       "39  Balerno Church    LRT     47 \n",
       "40  Canonmills        LRT     47 \n",
       "41  Cockburn Crescent LRT     47 \n",
       "42  Colinton          LRT     47 \n",
       "43  Craiglockhart     LRT     47 \n",
       "44  Currie            LRT     47 \n",
       "45  Hanover Street    LRT     47 \n",
       "46  Tollcross         LRT     47 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "route %>% \n",
    "    inner_join(stops, by=c(stop=\"id\")) %>% \n",
    "    inner_join(route %>%\n",
    "               inner_join(stops, by=c(stop=\"id\")),\n",
    "               by=c(company=\"company\", num=\"num\")\n",
    "              ) %>%\n",
    "    filter(name.x=='Craiglockhart' &\n",
    "           company=='LRT') %>%\n",
    "    distinct(name.y, company, num)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Find the routes involving two buses that can go from **Craiglockhart** to **Lochend**.\n",
    "Show the bus no. and company for the first bus, the name of the stop for the transfer,\n",
    "and the bus no. and company for the second bus.\n",
    "\n",
    "> _Hint_    \n",
    "> Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 36 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>num1</th><th scope=col>company1</th><th scope=col>transfer</th><th scope=col>num2</th><th scope=col>company2</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Leith         </td><td>34</td><td>LRT</td></tr>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Leith         </td><td>35</td><td>LRT</td></tr>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Leith         </td><td>87</td><td>LRT</td></tr>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Leith         </td><td>C5</td><td>SMT</td></tr>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Princes Street</td><td>65</td><td>LRT</td></tr>\n",
       "\t<tr><td>10</td><td>LRT</td><td>Princes Street</td><td>C5</td><td>SMT</td></tr>\n",
       "\t<tr><td>27</td><td>LRT</td><td>Canonmills    </td><td>34</td><td>LRT</td></tr>\n",
       "\t<tr><td>27</td><td>LRT</td><td>Canonmills    </td><td>35</td><td>LRT</td></tr>\n",
       "\t<tr><td>27</td><td>LRT</td><td>Crewe Toll    </td><td>20</td><td>LRT</td></tr>\n",
       "\t<tr><td>4 </td><td>LRT</td><td>Haymarket     </td><td>65</td><td>LRT</td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>35 </td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>42 </td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>46A</td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>65 </td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>87 </td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>87A</td><td>LRT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>London Road     </td><td>C5 </td><td>SMT</td></tr>\n",
       "\t<tr><td>45</td><td>LRT</td><td>Riccarton Campus</td><td>65 </td><td>LRT</td></tr>\n",
       "\t<tr><td>47</td><td>LRT</td><td>Canonmills      </td><td>34 </td><td>LRT</td></tr>\n",
       "\t<tr><td>47</td><td>LRT</td><td>Canonmills      </td><td>35 </td><td>LRT</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 36 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " num1 & company1 & transfer & num2 & company2\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 10 & LRT & Leith          & 34 & LRT\\\\\n",
       "\t 10 & LRT & Leith          & 35 & LRT\\\\\n",
       "\t 10 & LRT & Leith          & 87 & LRT\\\\\n",
       "\t 10 & LRT & Leith          & C5 & SMT\\\\\n",
       "\t 10 & LRT & Princes Street & 65 & LRT\\\\\n",
       "\t 10 & LRT & Princes Street & C5 & SMT\\\\\n",
       "\t 27 & LRT & Canonmills     & 34 & LRT\\\\\n",
       "\t 27 & LRT & Canonmills     & 35 & LRT\\\\\n",
       "\t 27 & LRT & Crewe Toll     & 20 & LRT\\\\\n",
       "\t 4  & LRT & Haymarket      & 65 & LRT\\\\\n",
       "\t ... & ... & ... & ... & ...\\\\\n",
       "\t 45 & LRT & London Road      & 35  & LRT\\\\\n",
       "\t 45 & LRT & London Road      & 42  & LRT\\\\\n",
       "\t 45 & LRT & London Road      & 46A & LRT\\\\\n",
       "\t 45 & LRT & London Road      & 65  & LRT\\\\\n",
       "\t 45 & LRT & London Road      & 87  & LRT\\\\\n",
       "\t 45 & LRT & London Road      & 87A & LRT\\\\\n",
       "\t 45 & LRT & London Road      & C5  & SMT\\\\\n",
       "\t 45 & LRT & Riccarton Campus & 65  & LRT\\\\\n",
       "\t 47 & LRT & Canonmills       & 34  & LRT\\\\\n",
       "\t 47 & LRT & Canonmills       & 35  & LRT\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 36 × 5\n",
       "\n",
       "| num1 &lt;chr&gt; | company1 &lt;chr&gt; | transfer &lt;chr&gt; | num2 &lt;chr&gt; | company2 &lt;chr&gt; |\n",
       "|---|---|---|---|---|\n",
       "| 10 | LRT | Leith          | 34 | LRT |\n",
       "| 10 | LRT | Leith          | 35 | LRT |\n",
       "| 10 | LRT | Leith          | 87 | LRT |\n",
       "| 10 | LRT | Leith          | C5 | SMT |\n",
       "| 10 | LRT | Princes Street | 65 | LRT |\n",
       "| 10 | LRT | Princes Street | C5 | SMT |\n",
       "| 27 | LRT | Canonmills     | 34 | LRT |\n",
       "| 27 | LRT | Canonmills     | 35 | LRT |\n",
       "| 27 | LRT | Crewe Toll     | 20 | LRT |\n",
       "| 4  | LRT | Haymarket      | 65 | LRT |\n",
       "| ... | ... | ... | ... | ... |\n",
       "| 45 | LRT | London Road      | 35  | LRT |\n",
       "| 45 | LRT | London Road      | 42  | LRT |\n",
       "| 45 | LRT | London Road      | 46A | LRT |\n",
       "| 45 | LRT | London Road      | 65  | LRT |\n",
       "| 45 | LRT | London Road      | 87  | LRT |\n",
       "| 45 | LRT | London Road      | 87A | LRT |\n",
       "| 45 | LRT | London Road      | C5  | SMT |\n",
       "| 45 | LRT | Riccarton Campus | 65  | LRT |\n",
       "| 47 | LRT | Canonmills       | 34  | LRT |\n",
       "| 47 | LRT | Canonmills       | 35  | LRT |\n",
       "\n"
      ],
      "text/plain": [
       "    num1 company1 transfer         num2 company2\n",
       "1   10   LRT      Leith            34   LRT     \n",
       "2   10   LRT      Leith            35   LRT     \n",
       "3   10   LRT      Leith            87   LRT     \n",
       "4   10   LRT      Leith            C5   SMT     \n",
       "5   10   LRT      Princes Street   65   LRT     \n",
       "6   10   LRT      Princes Street   C5   SMT     \n",
       "7   27   LRT      Canonmills       34   LRT     \n",
       "8   27   LRT      Canonmills       35   LRT     \n",
       "9   27   LRT      Crewe Toll       20   LRT     \n",
       "10  4    LRT      Haymarket        65   LRT     \n",
       "... ...  ...      ...              ...  ...     \n",
       "27  45   LRT      London Road      35   LRT     \n",
       "28  45   LRT      London Road      42   LRT     \n",
       "29  45   LRT      London Road      46A  LRT     \n",
       "30  45   LRT      London Road      65   LRT     \n",
       "31  45   LRT      London Road      87   LRT     \n",
       "32  45   LRT      London Road      87A  LRT     \n",
       "33  45   LRT      London Road      C5   SMT     \n",
       "34  45   LRT      Riccarton Campus 65   LRT     \n",
       "35  47   LRT      Canonmills       34   LRT     \n",
       "36  47   LRT      Canonmills       35   LRT     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bus1 <- route %>%\n",
    "    inner_join(stops, by=c(stop=\"id\")) %>% \n",
    "    inner_join(route %>%\n",
    "               inner_join(stops, by=c(stop=\"id\")),\n",
    "               by=c(company=\"company\", num=\"num\")\n",
    "              ) %>%\n",
    "    filter(name.x=='Craiglockhart')\n",
    "bus2 <- route %>%\n",
    "    inner_join(stops, by=c(stop=\"id\")) %>% \n",
    "    inner_join(route %>%\n",
    "               inner_join(stops, by=c(stop=\"id\")),\n",
    "               by=c(company=\"company\", num=\"num\")\n",
    "              ) %>%\n",
    "    filter(name.y=='Lochend')\n",
    "bus1 %>% \n",
    "    inner_join(bus2, by=c(stop.y=\"stop.x\")) %>%\n",
    "    select(num.x, company.x, name.y.x, num.y, company.y) %>%\n",
    "    `names<-`(c('num1', 'company1', 'transfer', 'num2', 'company2'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
